/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package bases;

import datos.Clientes;
import datos.Destinos;
import datos.Escalas;
import datos.Reservas;
import datos.ViajesOrganizados;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author mikygonka
 */
public class Bases {
    private Connection conexion;
    private String usuario="root";
    private String pass="";
    java.sql.Statement sentencia=null;

    /**
     * @param args the command line arguments
     */
    public void conectar(String BaseDatos) {
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conexion=DriverManager.getConnection("jdbc:mysql://localhost/agencia",usuario,pass);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
            
        
    }
    
    public  void insertarClientes(Clientes x,String tabla){
       sentencia=null;
        try {
            
            sentencia=conexion.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String sql="insert into "+tabla+" values ("+x.getCodigo()+",'"+x.getNombre()+"','"+x.getDomicilio()+"','"+x.getLocalidad()+"','"+x.getProvincia()+"');";
            int filasInsertadas=sentencia.executeUpdate(sql);
            System.out.println("Insertadas "+filasInsertadas+" filas");
            sentencia.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
 }
public  void insertarDestinos(Destinos x,String tabla){
       sentencia=null;
        try {
            
            sentencia=conexion.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String sql="insert into "+tabla+" values ("+x.getCodigo()+",'"+x.getCiudad()+"','"+x.getHoraLlegada()+"','"+x.getMedioTrans()+"');";
            int filasInsertadas=sentencia.executeUpdate(sql);
            System.out.println("Insertadas "+filasInsertadas+" filas");
            sentencia.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
 }

public  void insertarEscalas(Escalas x,String tabla){
       sentencia=null;
        try {
            
            sentencia=conexion.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String sql="insert into "+tabla+" values ("+x.getCodDest()+"','"+x.getCodigoViaje()+"','"+x.getNorden()+"');";
            int filasInsertadas=sentencia.executeUpdate(sql);
            System.out.println("Insertadas "+filasInsertadas+" filas");
            sentencia.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
 }

public  void insertarReservas(Reservas x,String tabla){
       sentencia=null;
        try {
            
            sentencia=conexion.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String sql="insert into "+tabla+" values ("+x.getCodCliente()+",'"+x.getCodViaje()+"','"+x.getNPerso()+"','"+x.getFechaReserva()+"','"+x.getFechaSal()+"');";
            int filasInsertadas=sentencia.executeUpdate(sql);
            System.out.println("Insertadas "+filasInsertadas+" filas");
            sentencia.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
 }
public  void insertarViajesorganizados(ViajesOrganizados x,String tabla){
       sentencia=null;
        try {
            
            sentencia=conexion.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String sql="insert into "+tabla+" values ("+x.getCodViaje()+",'"+x.getNPerso()+"','"+x.getFechaSal()+"','"+x.getFechaLlegada()+"','"+x.getPrecio()+"');";
            int filasInsertadas=sentencia.executeUpdate(sql);
            System.out.println("Insertadas "+filasInsertadas+" filas");
            sentencia.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
 }
public Object[][] listarOrdenadoDestinos(String tabla,String campo,String tipo){
     Object[][] datos=null;
     int filas=0;
     int i=0;
     
     
     try {
            ResultSet rs=null;
            java.sql.Statement stmr=null;
            
            stmr=conexion.createStatement();
            rs=stmr.executeQuery("SELECT count(CodDest) as total FROM "+tabla);
            rs.next();
            filas=Integer.parseInt(rs.getString("total"));
            datos=new Object[filas][4];
            String sql="SELECT * FROM "+tabla+" order by "+campo;
            if(tipo.equalsIgnoreCase("desc"))
                sql=sql+" desc;";
            rs=stmr.executeQuery(sql);
            
            while(rs.next()){
                datos[i][0]=rs.getString("CodDest");
                datos[i][1]=rs.getString("Ciudad");
                datos[i][2]=rs.getString("HoraLlegada");
                datos[i][3]=rs.getString("MedioTrans");
                i++;
            }
            rs.close();
            stmr.close();
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
         
   return datos;  
 }
public Object[][] listarOrdenadoClientes(String tabla,String campo,String tipo){
     Object[][] datos=null;
     int filas=0;
     int i=0;
     
     
     try {
            ResultSet rs=null;
            java.sql.Statement stmr=null;
            
            stmr=conexion.createStatement();
            rs=stmr.executeQuery("SELECT count(CodCliente) as total FROM "+tabla);
            rs.next();
            filas=Integer.parseInt(rs.getString("total"));
            datos=new Object[filas][5];
            String sql="SELECT * FROM "+tabla+" order by "+campo;
            if(tipo.equalsIgnoreCase("desc"))
                sql=sql+" desc;";
            rs=stmr.executeQuery(sql);
            
            while(rs.next()){
                datos[i][0]=rs.getString("CodCliente");
                datos[i][1]=rs.getString("Nombre");
                datos[i][2]=rs.getString("Domicilio");
                datos[i][3]=rs.getString("Localidad");
                datos[i][4]=rs.getString("Provincia");
                i++;
            }
            rs.close();
            stmr.close();
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
         
   return datos;  
 }

public Object[][] listarOrdenadoEscalas(String tabla,String campo,String tipo){
     Object[][] datos=null;
     int filas=0;
     int i=0;
     
     
     try {
            ResultSet rs=null;
            java.sql.Statement stmr=null;
            
            stmr=conexion.createStatement();
            rs=stmr.executeQuery("SELECT count(Codviaje) as total FROM "+tabla);
            rs.next();
            filas=Integer.parseInt(rs.getString("total"));
            datos=new Object[filas][3];
            String sql="SELECT * FROM "+tabla+" order by "+campo;
            if(tipo.equalsIgnoreCase("desc"))
                sql=sql+" desc;";
            rs=stmr.executeQuery(sql);
            
            while(rs.next()){
                datos[i][0]=rs.getString("CodViaje");
                datos[i][1]=rs.getString("CodDest");
                datos[i][2]=rs.getString("Norden");
                i++;
            }
            rs.close();
            stmr.close();
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
         
   return datos;  
 }
public Object[][] listarOrdenadoReservas(String tabla,String campo,String tipo){
     Object[][] datos=null;
     int filas=0;
     int i=0;
     
     
     try {
            ResultSet rs=null;
            java.sql.Statement stmr=null;
            
            stmr=conexion.createStatement();
            rs=stmr.executeQuery("SELECT count(CodCliente) as total FROM "+tabla);
            rs.next();
            filas=Integer.parseInt(rs.getString("total"));
            datos=new Object[filas][5];
            String sql="SELECT * FROM "+tabla+" order by "+campo;
            if(tipo.equalsIgnoreCase("desc"))
                sql=sql+" desc;";
            rs=stmr.executeQuery(sql);
            
            while(rs.next()){
                datos[i][0]=rs.getString("CodCliente");
                datos[i][1]=rs.getString("CodViaje");
                datos[i][2]=rs.getString("Nperso");
                datos[i][3]=rs.getString("FechaReserva");
                datos[i][4]=rs.getString("FechaSal");
                i++;
            }
            rs.close();
            stmr.close();
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
         
   return datos;  
 }
public Object[][] listarOrdenadoViajes(String tabla,String campo,String tipo){
     Object[][] datos=null;
     int filas=0;
     int i=0;
     
     
     try {
            ResultSet rs=null;
            java.sql.Statement stmr=null;
            
            stmr=conexion.createStatement();
            rs=stmr.executeQuery("SELECT count(CodViaje) as total FROM "+tabla);
            rs.next();
            filas=Integer.parseInt(rs.getString("total"));
            datos=new Object[filas][2];
            String sql="SELECT * FROM "+tabla+" order by "+campo;
            if(tipo.equalsIgnoreCase("desc"))
                sql=sql+" desc;";
            rs=stmr.executeQuery(sql);
            
            while(rs.next()){
                datos[i][0]=rs.getString("CodViaje");
                datos[i][1]=rs.getString("Descripcion");
                i++;
            }
            rs.close();
            stmr.close();
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
         
   return datos;  
 }
public Object[][] listarOrdenadoViajesOrganizados(String tabla,String campo,String tipo){
     Object[][] datos=null;
     int filas=0;
     int i=0;
     
     
     try {
            ResultSet rs=null;
            java.sql.Statement stmr=null;
            
            stmr=conexion.createStatement();
            rs=stmr.executeQuery("SELECT count(CodViaje) as total FROM "+tabla);
            rs.next();
            filas=Integer.parseInt(rs.getString("total"));
            datos=new Object[filas][5];
            String sql="SELECT * FROM "+tabla+" order by "+campo;
            if(tipo.equalsIgnoreCase("desc"))
                sql=sql+" desc;";
            rs=stmr.executeQuery(sql);
            
            while(rs.next()){
                datos[i][0]=rs.getString("CodViaje");
                datos[i][1]=rs.getString("NPerso");
                datos[i][2]=rs.getString("FechaSal");
                datos[i][3]=rs.getString("FechaLlega");
                datos[i][4]=rs.getString("Precio");
                i++;
            }
            rs.close();
            stmr.close();
        } catch (SQLException ex) {
            Logger.getLogger(Bases.class.getName()).log(Level.SEVERE, null, ex);
        }
         
   return datos;  
 }
    
}
